package org.zoomdev.zoom.dao.impl;

import org.apache.commons.lang3.NotImplementedException;
import org.apache.commons.lang3.StringUtils;
import org.zoomdev.zoom.common.expression.Symbol;
import org.zoomdev.zoom.common.filter.Filter;
import org.zoomdev.zoom.dao.DaoException;
import org.zoomdev.zoom.dao.Entity;
import org.zoomdev.zoom.dao.Record;
import org.zoomdev.zoom.dao.Select;
import org.zoomdev.zoom.dao.Sql;
import org.zoomdev.zoom.dao.SqlBuilder;
import org.zoomdev.zoom.dao.adapters.EntityField;
import org.zoomdev.zoom.dao.adapters.StatementAdapter;
import org.zoomdev.zoom.dao.driver.SqlDriver;
import org.zoomdev.zoom.dao.struct.JoinMeta;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * 这个类不做任何字段的过滤和处理
 *
 * @author jzoom
 */
public class SimpleSqlBuilder implements SqlBuilder {
    public static final char COMMA = ',';  //comma
    protected static final char SPACE = ' ';
    private static final String AND = " AND ";
    private static final String OR = " OR ";
    private final List<String> tables = new ArrayList<>();
    //private static final Log log = LogFactory.getLog(SimpleSqlBuilder.class);
    protected StringBuilder sql;
    protected StringBuilder where;
    protected StringBuilder orderBy;
    protected StringBuilder groupBy;
    protected StringBuilder join;
    protected StringBuilder select;
    protected List<Object> values;
    protected List<Object> updateValues;
    protected List<StatementAdapter> adapters;
    protected StringBuilder having;
    protected SqlDriver driver;
    protected Record record;
    boolean condition;
    private Map<String, Object> raw;

    public SimpleSqlBuilder(SqlDriver driver) {
        this.driver = driver;
        sql = new StringBuilder();
        where = new StringBuilder();
        orderBy = new StringBuilder();
        join = new StringBuilder();
        groupBy = new StringBuilder();
        select = new StringBuilder();
        having = new StringBuilder();
        record = new Record();
        values = new ArrayList<>();
        adapters = new ArrayList<>();
        updateValues = new ArrayList<>();
    }

    public static String getSql(String sql, List<Object> values) {
        return getSql(sql, values.toArray(new Object[values.size()]));
    }

    public static String getSql(String sql, Object[] values) {
        return String.format(sql
                .replace("%", "%%")
                .replace("?", "'%s'"), values);
    }

    public void buildLimit(int position, int size) {
        driver.buildLimit(sql, values, position, size);
    }

    public Set<String> getAvailableFields(EntityField[] entityFields) {
        Set<String> list = new LinkedHashSet<>();

        for (EntityField entityField : entityFields) {
            list.add(entityField.getFieldName());
        }

        // 对于Record来说，是没有这个字段的
//        if (namesMap != null) {
//            list.addAll(namesMap.keySet());
//
//        }

        return list;
    }

    public EntityField[] convertSelect(Entity entity, String select) {
        if (select != null) {
            String[] fields = select.split(",");
            EntityField[] entityFields = new EntityField[fields.length];
            int index = 0;
            for (String key : fields) {
                EntityField entityField = entity.tryToFind(key);
                if (entityField == null) {
                    throw new DaoException("找不到" + key + "对应的字段,所有可能的字段为" + StringUtils.join(getAvailableFields(entity.getEntityFields())));
                }
                entityFields[index++] = entityField;
            }
            return entityFields;
        }

        if (entity.getTableCount() == 1 && tables.size() > 0) {
            List<EntityField> all = new ArrayList<>();
            Collections.addAll(all, entity.getEntityFields());
            for (String table : tables) {
                Entity entity0 = entity.addTable(table);
                Collections.addAll(all, entity0.getEntityFields());
            }


            return all.toArray(new EntityField[all.size()]);
        }

        return entity.getEntityFields();
    }

    public void buildSelect(String rawSelect, String table) {
        final StringBuilder sql = this.sql;
        sql.append("SELECT ")
                .append(rawSelect)
                .append(" FROM ");
        driver.protect(sql, table);
        sql.append(join);
        if (where.length() > 0) {
            sql.append(" WHERE ");
        }
        sql.append(where);
        sql.append(groupBy);
        if (having.length() > 0) {
            sql.append(" HAVING ").append(having);
        }
        sql.append(orderBy);
    }

    public EntityField[] buildSelect(Entity entity, String select) {
        EntityField[] entityFields = convertSelect(entity, select);
        buildSelect(entity, entityFields);
        return entityFields;
    }

    public void buildSelect(Entity entity, EntityField[] entityFields) {
        final StringBuilder sql = this.sql;
        sql.append("SELECT ");
        if (entityFields == null) {
            sql.append("*");
        } else {
            if (entity.getTableCount() > 1 || join.length() > 0) {
                for (EntityField entityField : entityFields) {
                    driver.protect(sql, entityField.getTable());
                    sql.append(".");
                    driver.protect(sql, entityField.getColumn());
                    sql.append(",");
                }
            } else {
                for (EntityField entityField : entityFields) {
                    driver.protect(sql, entityField.getColumn());
                    sql.append(",");
                }
            }
            sql.setLength(sql.length() - 1);
        }

        sql.append(" FROM ");
        driver.protect(sql, entity.getTableName());

        JoinMeta[] joinMetas = entity.getJoinMetas();
        if (joinMetas != null) {
            for (JoinMeta joinMeta : joinMetas) {
                sql.append(SPACE)
                        .append(joinMeta.getType())
                        .append(" JOIN ")
                        .append(joinMeta.getTable())
                        .append(" ON ")
                        .append(joinMeta.getOn());
            }
        } else {
            sql.append(join);
        }


        if (where.length() > 0) {
            sql.append(" WHERE ");
        }
        sql.append(where);
        sql.append(groupBy);
        if (having.length() > 0) {
            sql.append(" HAVING ").append(having);
        }
        sql.append(orderBy);
    }

    public void setRaw(String key, Object value) {
        if (raw == null) {
            raw = new HashMap<>();
        }
        raw.put(key, value);
    }

    public void entityCondition(Entity entity, Object data) {
        // 主键
        for (EntityField adapter : entity.getPrimaryKeys()) {
            Object value = adapter.get(data);
            if (value != null) {
                where(adapter.getColumn(), value);
                adapters.add(adapter.getStatementAdapter());
            }
        }
    }

    public void buildUpdate(Entity entity,
                            Object data,
                            Filter<EntityField> filter,
                            boolean ignoreNull,
                            boolean ignoreEntityCondition) {

        sql.append("UPDATE ");
        driver.protect(sql, entity.getTableName());
        if (!ignoreEntityCondition) {
            entityCondition(entity, data);
        }

        int index = 0;
        for (EntityField field : entity.getEntityFields()) {
            if (filter == null || filter.accept(field)) {
                Object value = field.get(data);
                if (value == null && ignoreNull) continue;
                if (index == 0) {
                    sql.append(" SET ");
                } else {
                    sql.append(COMMA);
                }
                adapters.add(field.getStatementAdapter());
                updateValues.add(field.get(data));
                ++index;
                driver.protect(sql, field.getColumn()).append("=?");
            }
        }

        if (where.length() > 0) {
            sql.append(" WHERE ").append(where);
        } else {
            throw new DaoException("update至少需要一个where条件");
        }

    }

    public void prepare(PreparedStatement ps) throws SQLException {
        int index = 0;
        //update在前
        for (int i = 0, c = adapters.size(); i < c; ++i) {
            index = i + 1;

            Object value = updateValues.get(i);
            StatementAdapter adapter = adapters.get(i);
            adapter.adapt(ps, index, value);
        }

        //where在后
        for (int i = 0, c = values.size(); i < c; ++i) {
            int currentIndex = index + i + 1;
            Object value = values.get(i);
            ps.setObject(currentIndex, value);
        }
    }

    public void buildInsert() {

    }

    public void buildDelete() {

    }

    @Override
    public String printSql() {
        return getSql(sql.toString(), values);
    }

    public void insertOrUpdate(Entity entity, String[] keys) {
        values.clear();
        sql.setLength(0);
        driver.insertOrUpdate(sql, values, entity.getTableName(), record, keys);
    }

    public void clear(boolean all) {
        if (all) {
            where.setLength(0);
            join.setLength(0);
            groupBy.setLength(0);
            values.clear();
            having.setLength(0);
            adapters.clear();
            updateValues.clear();
            tables.clear();
        }


        sql.setLength(0);
        orderBy.setLength(0);
        record.clear();
        if (raw != null) {
            raw.clear();
            raw = null;
        }
        select.setLength(0);
    }

    private void whereRelation(StringBuilder where, String relation) {
        if (condition) {
            condition = false;
        } else {
            if (where.length() == 0) {
            } else {
                where.append(relation);
            }
        }

    }

    @Override
    public SqlBuilder like(String key, Like like, Object value) {
        return relationLike(key, like, value, AND, false);
    }

    private SqlBuilder relationLike(String key, Like like, Object value, String relation, boolean not) {
        assert (key != null);

        checkValue(key, value);
        whereRelation(where, relation);

        where.append(key);
        if (not) {
            where.append(" NOT");
        }
        where.append(" LIKE ?");
        addValue(key, like.toValue(value));
        return this;
    }

    @Override
    public SqlBuilder orLike(String key, Like like, Object value) {
        return relationLike(key, like, value, OR, false);
    }

    @Override
    public SqlBuilder exists(Select select) {
        return null;
    }

    private void checkValue(String key, Object value) {
        if (value == null) {
            throw new DaoException(String.format("名称为%s的字段值为null,请使用whereNull或者whereNotNull版本", key));
        }
    }

    @Override
    public SqlBuilder where(String key, Symbol symbol, Object value) {
        assert (key != null && symbol != null);
        return whereImpl(where, key, symbol, value, AND);
    }

    protected SqlBuilder whereImpl(StringBuilder where, String key, Symbol symbol, Object value, String relation) {
        checkValue(key, value);
        whereRelation(where, relation);
        where.append(key).append(symbol.value()).append("?");
        addValue(key, value);
        return this;
    }

    @Override
    public SqlBuilder where(String key, Object value) {
        return whereImpl(where, key, Symbol.EQ, value, AND);
    }

    @Override
    public SqlBuilder orWhere(String key, Object value) {
        return orWhere(key, Symbol.EQ, value);
    }

    @Override
    public SqlBuilder orWhere(String key, Symbol symbol, Object value) {
        return whereImpl(where, key, symbol, value, OR);
    }

    @Override
    public SqlBuilder whereNull(String key) {
        return whereNull(key, AND, false);
    }

    private SqlBuilder whereNull(String key, String relation, boolean not) {

        StringBuilder where = this.where;
        whereRelation(where, relation);
        where.append(key);
        where.append(" IS ");
        if (not) where.append("NOT ");
        where.append("NULL");

        return this;
    }

    @Override
    public SqlBuilder whereNotNull(String key) {
        return whereNull(key, AND, true);
    }

    @Override
    public <E> SqlBuilder whereNotIn(String key, Collection<E> values) {
        return whereIn(key, AND, true, values);
    }

    protected <E> SqlBuilder whereIn(String key, String relation, boolean not, Collection<E> values) {
        StringBuilder where = this.where;
        whereRelation(where, relation);
        where.append(key);
        if (not) {
            where.append(" NOT");
        }
        where.append(" IN (");
        boolean first = true;
        for (Object object : values) {
            if (first) {
                first = false;
            } else {
                where.append(",");
            }
            where.append("?");
            this.addValue(key, object);
        }
        where.append(')');

        return this;
    }

    @Override
    public <E> SqlBuilder whereIn(String key, Collection<E> values) {
        return whereIn(key, AND, false, values);
    }

    @Override
    public <E> SqlBuilder whereIn(String key, Select select) {
        return null;
    }

    @Override
    public SqlBuilder join(String otherTable, String on) {
        return join(otherTable, on, "INNER");
    }


    public SqlBuilder join(String table, String on, String type) {
        if (type == null) {
            type = "INNER";
        }
        tables.add(table);
        join.append(SPACE)
                .append(type)
                .append(" JOIN ");

        driver.protect(join, table);
        join.append(" ON ")
                .append(on);
        return this;
    }


    @Override
    public SqlBuilder join(String table, String on, JoinType type) {
        return join(table, on, type.value());
    }

    @Override
    public SqlBuilder union(Select select) {
        return null;
    }

    @Override
    public SqlBuilder unionAll(Select select) {
        return null;
    }

    private void addValue(String key, Object value) {
        this.values.add(value);
    }

    @Override
    public SqlBuilder orderBy(String field, Sort sort) {
        assert (sort != null && field != null);
        if (orderBy.length() != 0) {
            orderBy.append(',');
        } else {
            orderBy.append(" ORDER BY ");
        }
        orderBy.append(field).append(SPACE).append(sort.value());
        return this;
    }

    @Override
    public SqlBuilder groupBy(String group) {
        groupBy.append(" GROUP BY ").append(group);
        return this;
    }


    @Override
    public SqlBuilder selectCount(String alias) {
        selectFunc("*", alias, "COUNT");
        return this;
    }

    public SqlBuilder selectRaw(String fields) {
        if (select.length() > 0) {
            select.append(",");
        }
        select.append(fields);
        return this;
    }

    @Override
    public SqlBuilder from(String table) {
        return null;
    }

    @Override
    public SqlBuilder limit(int limit) {
        return null;
    }

    @Override
    public SqlBuilder limit(int position, int limit) {
        return null;
    }

    @Override
    public SqlBuilder select(String fields) {
        parseSelect(select, fields);
        return this;
    }

    public SqlBuilder select(Iterable<String> fields) {
        parseSelect(select, fields);
        return this;
    }

    /**
     * select 中的形式有 函数(字段,字段) as 字段 , 字段 as 字段,
     *
     * @param sql
     * @param select
     */
    protected void parseSelect(StringBuilder sql, String select) {
        if (select.isEmpty()) return;
        String[] parts = select.split(",");
        parseSelect(sql, parts);
    }

    protected void parseSelect(StringBuilder sql, String[] parts) {
        boolean first = true;
        for (String part : parts) {
            if (first) {
                first = false;
            } else {
                sql.append(",");
            }
            parseSelectColumn(sql, part);
        }
    }

    protected void parseSelect(StringBuilder sql, Iterable<String> parts) {
        boolean first = true;
        for (String part : parts) {
            if (first) {
                first = false;
            } else {
                sql.append(",");
            }
            parseSelectColumn(sql, part);
        }
    }

    /**
     * column 里面的形式为 SELECT 中的字段,所以可能需要在子类过滤一下非法查询
     *
     * @param sql
     * @param column
     */
    protected void parseSelectColumn(StringBuilder sql, String column) {
        if (column.contains("'")) {
            throw new DaoException("字段中不能包含',具体的值必须写在参数中");
        }
        sql.append(column.trim());
    }

    /**
     * condition 里面的形式为 WHERE 中的查询条件,所以可能需要在子类过滤一下非法查询
     *
     * @param where
     * @param condition
     */
    protected void parseCondition(StringBuilder where, String condition) {
        where.append(condition);
    }

    @Override
    public SqlBuilder set(String key, Object value) {
        record.put(key, value);
        return this;
    }

    @Override
    public SqlBuilder into(String table, String... fields) {
        return null;
    }

    @Override
    public SqlBuilder replace(String table, String... fields) {
        return null;
    }

    @Override
    public SqlBuilder values(Object... args) {
        return null;
    }

    @Override
    public SqlBuilder select(Select select) {
        return null;
    }

    @Override
    public SqlBuilder onDuplicateKeyUpdate() {
        return null;
    }


    @Override
    public SqlBuilder setAll(Map<String, Object> data) {
        record.putAll(data);
        return this;
    }

    @Override
    public SqlBuilder clear() {
        clear(true);
        return this;
    }

    @Override
    public List<Object> values() {
        return values;
    }

    @Override
    public String sql() {
        return sql.toString();
    }

    /**
     * 这个版本暂时原封不动，下个版本解析
     *
     * @param key
     * @param values
     * @return
     */
    @Override
    public SqlBuilder whereCondition(String key, Object... values) {
        // parse the name ,it should be  group of
        // a >/</=/>=/<=/<> ?
        // a in ( ?,? )
        // not a is null
        // a is null
        // (group)
        // group and group
        // group or group
        // a like '%xx%'
        // a not like '%xx%'
        // a in/exists (select xx from b) is not allowed!

        if (StringUtils.countMatches(key, '?') != values.length) {
            throw new DaoException("?的数量与参数数量不符:" + key);
        }

        parseCondition(where, key);
        Collections.addAll(this.values, values);
        return this;
    }


    void conditionWhere(Sql<?> sql, Condition condition) {
        where.append("(");
        this.condition = true;
        condition.where(sql);
        if (this.condition) {
            throw new DaoException("Condition下至少需要一个条件");
        }
        where.append(")");
    }

    @Override
    public SqlBuilder where(Condition condition) {
        return where(this, condition);
    }

    @Override
    public <E> SqlBuilder whereIn(String key, Iterable<E> values) {
        return null;
    }

    @Override
    public SqlBuilder where(String key) {
        return null;
    }

    @Override
    public SqlBuilder orWhere(Condition condition) {
        return orWhere(this, condition);

    }


    public SqlBuilder where(Sql<?> sql, Condition condition) {
        whereRelation(where, AND);
        conditionWhere(sql, condition);
        return this;
    }

    public SqlBuilder orWhere(Sql<?> sql, Condition condition) {
        whereRelation(where, OR);
        conditionWhere(this, condition);
        return this;

    }


    @Override
    public SqlBuilder having(String key, Symbol symbol, Object value) {


        whereImpl(having, key, symbol, value, AND);

        return this;

    }

    @Override
    public SqlBuilder notLike(String key, Like like, Object value) {

        return relationLike(key, like, value, AND, false);
    }

    @Override
    public SqlBuilder union(SqlBuilder builder) {
        throw new NotImplementedException("union");
    }

    @Override
    public SqlBuilder unionAll(SqlBuilder builder) {
        throw new NotImplementedException("unionAll");
    }


    /**
     * 使用本方法调用 SELECT max(ID) AS ID_ 之类的select字段
     * {@link SimpleSqlBuilder#selectMax}
     *
     * @param field
     * @param function
     * @return
     */
    protected SqlBuilder selectFunc(String field, String alias, String function) {
        if (select.length() > 0) {
            select.append(",");
        }
        select.append(function).append("(");
        parseSelect(select, field);
        select.append(")");
        select.append(" AS ").append(alias);
        return this;
    }

    @Override
    public SqlBuilder selectMax(String field, String alias) {
        return selectFunc(field, alias, "MAX");
    }

    @Override
    public SqlBuilder selectSum(String field, String alias) {
        return selectFunc(field, alias, "SUM");
    }

    @Override
    public SqlBuilder selectMin(String field, String alias) {
        return selectFunc(field, alias, "MIN");
    }

    @Override
    public SqlBuilder selectAvg(String field, String alias) {
        return selectFunc(field, alias, "AVG");
    }


}
